﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BOMS.DBUtility;
using System.Data;
using System.Data.SqlClient;
using BOMS.Model;//请先添加引用
using MySql.Data.MySqlClient;
using BOMS.Utility;
    namespace BOMS.DAL
    {
        /// <summary>
        /// 数据访问类S_Warning。
        /// </summary>
        public class S_Warning
        {
            public S_Warning()
            { }
            #region  成员方法
            /// <summary>
            /// 是否存在该记录
            /// </summary>
            public bool Exists(int ID)
            {
                bool isExists = false;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from S_Warning");
                strSql.Append(" where ID=@ID ");
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
                    parameters[0].Value = ID;
                    isExists = DbHelperSQL.Exists(strSql.ToString(), parameters);
                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {
					new MySqlParameter("@ID", MySqlDbType.Int32,4)};
                    parameters[0].Value = ID;
                    isExists = DbHelperMySql.Exists(strSql.ToString(), parameters);
                }
                return isExists;
            }


            /// <summary>
            /// 增加一条数据
            /// </summary>
            public int Add(BOMS.Model.S_Warning model)
            {
                object obj = new object();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into S_Warning(");
                strSql.Append("EquipID,BatteryGroup,BatteryID,WarningTime,WarningStatus,Note,IsActive)");
                strSql.Append(" values (");
                strSql.Append("@EquipID,@BatteryGroup,@BatteryID,@WarningTime,@WarningStatus,@Note,@IsActive)");
                strSql.Append(";select @@IDENTITY");
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {
					new SqlParameter("@EquipID", SqlDbType.NVarChar,50),
					new SqlParameter("@BatteryGroup", SqlDbType.NVarChar,30),
					new SqlParameter("@BatteryID", SqlDbType.Int,4),
					new SqlParameter("@WarningTime", SqlDbType.DateTime),
					new SqlParameter("@WarningStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@Note", SqlDbType.NVarChar,100),
					new SqlParameter("@IsActive", SqlDbType.Bit,1)};
                    parameters[0].Value = model.EquipID;
                    parameters[1].Value = model.BatteryGroup;
                    parameters[2].Value = model.BatteryID;
                    parameters[3].Value = model.WarningTime;
                    parameters[4].Value = model.WarningStatus;
                    parameters[5].Value = model.Note;
                    parameters[6].Value = model.IsActive;

                    obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {
					new MySqlParameter("@EquipID", MySqlDbType.VarChar,50),
					new MySqlParameter("@BatteryGroup", MySqlDbType.VarChar,30),
					new MySqlParameter("@BatteryID", MySqlDbType.Int32,4),
					new MySqlParameter("@WarningTime", MySqlDbType.DateTime),
					new MySqlParameter("@WarningStatus", MySqlDbType.VarChar,20),
					new MySqlParameter("@Note", MySqlDbType.VarChar,100),
					new MySqlParameter("@IsActive", MySqlDbType.Bit,1)};
                    parameters[0].Value = model.EquipID;
                    parameters[1].Value = model.BatteryGroup;
                    parameters[2].Value = model.BatteryID;
                    parameters[3].Value = model.WarningTime;
                    parameters[4].Value = model.WarningStatus;
                    parameters[5].Value = model.Note;
                    parameters[6].Value = model.IsActive;

                    obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
                }
                
                if (obj == null)
                {
                    return 1;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
            /// <summary>
            /// 更新一条数据
            /// </summary>
            public void Update(BOMS.Model.S_Warning model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update S_Warning set ");
                strSql.Append("EquipID=@EquipID,");
                strSql.Append("BatteryGroup=@BatteryGroup,");
                strSql.Append("BatteryID=@BatteryID,");
                strSql.Append("WarningTime=@WarningTime,");
                strSql.Append("WarningStatus=@WarningStatus,");
                strSql.Append("Note=@Note,");
                strSql.Append("IsActive=@IsActive");
                strSql.Append(" where ID=@ID ");
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@EquipID", SqlDbType.NVarChar,50),
					new SqlParameter("@BatteryGroup", SqlDbType.NVarChar,30),
					new SqlParameter("@BatteryID", SqlDbType.Int,4),
					new SqlParameter("@WarningTime", SqlDbType.DateTime),
					new SqlParameter("@WarningStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@Note", SqlDbType.NVarChar,100),
					new SqlParameter("@IsActive", SqlDbType.Bit,1)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.EquipID;
                    parameters[2].Value = model.BatteryGroup;
                    parameters[3].Value = model.BatteryID;
                    parameters[4].Value = model.WarningTime;
                    parameters[5].Value = model.WarningStatus;
                    parameters[6].Value = model.Note;
                    parameters[7].Value = model.IsActive;

                    DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {
					new MySqlParameter("@ID", MySqlDbType.Int32,4),
					new MySqlParameter("@EquipID", MySqlDbType.VarChar,50),
					new MySqlParameter("@BatteryGroup", MySqlDbType.VarChar,30),
					new MySqlParameter("@BatteryID", MySqlDbType.Int32,4),
					new MySqlParameter("@WarningTime", MySqlDbType.DateTime),
					new MySqlParameter("@WarningStatus", MySqlDbType.VarChar,20),
					new MySqlParameter("@Note", MySqlDbType.VarChar,100),
					new MySqlParameter("@IsActive", MySqlDbType.Bit,1)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.EquipID;
                    parameters[2].Value = model.BatteryGroup;
                    parameters[3].Value = model.BatteryID;
                    parameters[4].Value = model.WarningTime;
                    parameters[5].Value = model.WarningStatus;
                    parameters[6].Value = model.Note;
                    parameters[7].Value = model.IsActive;

                    DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
                }
            }
            /// <summary>
            /// 求电池（组）日志表中，当前最大和最小时间
            /// </summary>
            /// <param name="Equip">设备ID</param>
            /// <param name="BatteryGrp">组号</param>
            /// <param name="BatteryID">此值为-1时，代表电池组</param>
            /// <returns>时间数组[minDtm,maxDtm]</returns>
            public DateTime?[] GetRealLogTime(string Equip, string BatteryGrp, int BatteryID)
            {
                DateTime?[] dtms = new DateTime?[2];
                DataTable dt = new DataTable();
                string sql = "";
                if (BatteryID == -1)
                {
                    //电池组
                    sql = @"select  min(logtime) as DtmMin,max(logtime) as DtmMax from S_BatgrpSubLog where EquipID=@EquipID and BatteryGroup=@BatteryGroup";
                }
                else
                {
                    sql = @"select  min(logtime) as DtmMin,max(logtime) as DtmMax from S_BatSubLog where EquipID=@EquipID and BatteryGroup=@BatteryGroup and BatteryID=@BatteryID";
                }
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {					
					new SqlParameter("@EquipID", SqlDbType.NVarChar,50),
					new SqlParameter("@BatteryGroup", SqlDbType.NVarChar,30),
					new SqlParameter("@BatteryID", SqlDbType.Int,4)};
                    parameters[0].Value = Equip;
                    parameters[1].Value = BatteryGrp;
                    parameters[2].Value = BatteryID;
                     dt = DbHelperSQL.Query(sql, parameters).Tables[0];

                     if (dt.Rows[0]["DtmMin"] != DBNull.Value)
                     {
                         dtms[0] = (DateTime)dt.Rows[0]["DtmMin"];
                     }
                     else
                     {
                         dtms[0] = null;
                     }
                     if (dt.Rows[0]["DtmMax"] != DBNull.Value)
                     {
                         dtms[1] = (DateTime)dt.Rows[0]["DtmMax"];
                     }
                     else
                     {
                         dtms[1] = null;
                     }

                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {					
					new MySqlParameter("@EquipID", MySqlDbType.VarChar,50),
					new MySqlParameter("@BatteryGroup", MySqlDbType.VarChar,30),
					new MySqlParameter("@BatteryID", MySqlDbType.Int32,4)};
                    parameters[0].Value = Equip;
                    parameters[1].Value = BatteryGrp;
                    parameters[2].Value = BatteryID;
                    dt = DbHelperMySql.Query(sql, parameters).Tables[0];

                    if (dt.Rows[0]["DtmMin"] != DBNull.Value)
                    {
                        dtms[0] = ((MySql.Data.Types.MySqlDateTime)dt.Rows[0]["DtmMin"]).GetDateTime();
                    }
                    else
                    {
                        dtms[0] = null;
                    }
                    if (dt.Rows[0]["DtmMax"] != DBNull.Value)
                    {
                        dtms[1] = ((MySql.Data.Types.MySqlDateTime)dt.Rows[0]["DtmMax"]).GetDateTime();
                    }
                    else
                    {
                        dtms[1] = null;
                    }
                }
                
                return dtms;
            }
            /// <summary>
            /// 统计某电池（组）一段时间内的报警条数
            /// </summary>
            /// <param name="Equip"></param>
            /// <param name="BatteryGrp"></param>
            /// <param name="BatteryID">若-1，则表示电池组</param>
            /// <param name="dtbegin"></param>
            /// <param name="dtend"></param>
            /// <returns></returns>
            public DataTable GetWarningStatus(string Equip,string BatteryGrp,int BatteryID,DateTime dtbegin,DateTime dtend)
            {
                DataTable dt = new DataTable();
                StringBuilder sb = new StringBuilder("select WarningStatus,count(1) as StatusCount from S_Warning where WarningTime>=@dtmbegin and WarningTime<=@dtmend and isactive=0");
                if (Equip != "")
                {
                    sb.Append(" and EquipID=@EquipID ");
                }
                if (BatteryGrp != "")
                {
                    sb.Append(" and BatteryGroup=@BatteryGroup ");
                }

                if (BatteryID!=-1)
                {
                    sb.Append(" and BatteryID=@BatteryID ");
                }
                sb.Append("  group by WarningStatus");
//                else
//                {
//                    sql = @"select WarningStatus,count(1) as StatusCount from S_Warning where EquipID=@EquipID and BatteryGroup=@BatteryGroup and BatteryID=@BatteryID
//                            and WarningTime>=@dtmbegin and WarningTime<=@dtmend
//                            group by WarningStatus";
//                }
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {					
					new SqlParameter("@EquipID", SqlDbType.NVarChar,50),
					new SqlParameter("@BatteryGroup", SqlDbType.NVarChar,30),
					new SqlParameter("@BatteryID", SqlDbType.Int,4),
					new SqlParameter("@dtmbegin", SqlDbType.DateTime),
					new SqlParameter("@dtmend",SqlDbType.DateTime)};
                    parameters[0].Value = Equip;
                    parameters[1].Value = BatteryGrp;
                    parameters[2].Value = BatteryID;
                    parameters[3].Value = dtbegin;
                    parameters[4].Value = dtend;
                    dt=DbHelperSQL.Query(sb.ToString(), parameters).Tables[0];
                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {					
					new MySqlParameter("@EquipID", MySqlDbType.VarChar,50),
					new MySqlParameter("@BatteryGroup", MySqlDbType.VarChar,30),
					new MySqlParameter("@BatteryID", MySqlDbType.Int32,4),
					new MySqlParameter("@dtmbegin", MySqlDbType.DateTime),
					new MySqlParameter("@dtmend",MySqlDbType.DateTime)};
                    parameters[0].Value = Equip;
                    parameters[1].Value = BatteryGrp;
                    parameters[2].Value = BatteryID;
                    parameters[3].Value = dtbegin;
                    parameters[4].Value = dtend;
                    dt = DbHelperMySql.Query(sb.ToString(), parameters).Tables[0];
                }
                return dt;
            }
             public DataTable GetNewWarningData(int timespan,out int NowWarningCount)
             {
                 DataTable dt = new DataTable();
                 
                 if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                 {
                     StringBuilder strSql = new StringBuilder();
                     strSql.Append("select @NowWarningCount=count(1) from S_Warning where IsActive=1; ");
                     strSql.Append("select w.ID,isnull(s.HostName,'') as HostName,w.EquipID,w.BatteryGroup,w.BatteryID,w.WarningTime,w.WarningNO,w.WarningStatus,w.Note,w.IsActive ");
                     strSql.Append(" FROM S_Warning w left outer join M_SubLans s on w.EquipID=s.Mac ");
                     strSql.Append(" where IsActive=1 and w.WarningTime>dateadd(ss,0-@TS,getdate()) order by w.WarningTime desc;");

                     SqlParameter[] parameters = {					
					new SqlParameter("@NowWarningCount", SqlDbType.Int,4),
					new SqlParameter("@TS", SqlDbType.Int,4),
                                                 };
                     parameters[0].Direction = ParameterDirection.Output;
                     parameters[1].Value = timespan;
                     dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
                     NowWarningCount = (int)parameters[0].Value;
                 }
                 else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                 {
                     StringBuilder strSql = new StringBuilder();
                     strSql.Append("select count(1) as RecordCount from S_Warning where IsActive=1; ");
                     strSql.Append("select w.ID,s.HostName as HostName,w.EquipID,w.BatteryGroup,w.BatteryID,w.WarningTime,w.WarningNO,w.WarningStatus,w.Note,w.IsActive ");
                     strSql.Append(" FROM S_Warning w left outer join M_SubLans s on w.EquipID=s.Mac ");
                     strSql.Append(" where IsActive=1 and w.WarningTime>date_add(now(),interval 0-@TS second) order by w.WarningTime desc;");

                     MySqlParameter[] parameters = {				
					
					    new MySqlParameter("@TS", MySqlDbType.Int32,4)                   
                                                   };
                     parameters[0].Value = timespan;
                     DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters);
                     dt = ds.Tables[1] ;
                     NowWarningCount = Convert.ToInt32(ds.Tables[0].Rows[0]["RecordCount"]);
                 }
                 else
                 {
                     NowWarningCount = 0;
                 }
                 
                 return dt;
             }
            public void AssignWarningStatus(DataRow dr,WarningStatus model)
            {
                if (dr["WarningStatus"] != DBNull.Value)
                {
                    model.Status = dr["WarningStatus"].ToString();
                }
                if (dr["StatusCount"] != DBNull.Value)
                {
                    model.Count = Convert.ToInt32(dr["StatusCount"]);
                }
            }

            /// <summary>
            /// 得到一个对象实体
            /// </summary>
            public BOMS.Model.S_Warning GetModel(int ID)
            {
                DataSet ds = new DataSet();
                BOMS.Model.S_Warning model = new BOMS.Model.S_Warning();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select a.ID,EquipID,BatteryGroup,BatteryID,WarningTime,WarningNO,WarningStatus,Note,IsActive,b.HostName from S_Warning a left outer join M_SubLans b on a.EquipID=b.Mac ");
                strSql.Append("where a.ID=@ID ");
                if (DbHelper.DBType == ConstSet.DBTYPE_MSSQL)
                {
                    SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
                    parameters[0].Value = ID;
                     ds = DbHelperSQL.Query(strSql.ToString(), parameters);
                }
                else if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                {
                    MySqlParameter[] parameters = {
					new MySqlParameter("@ID", MySqlDbType.Int32,4)};
                    parameters[0].Value = ID;
                    ds = DbHelperMySql.Query(strSql.ToString(), parameters);
                }
                if (ds.Tables[0].Rows.Count > 0)
                {
                    
                    AssignModel(ds.Tables[0].Rows[0], model);
                    return model;
                }
                else
                {
                    return null;
                }
            }
            public void AssignModel(DataRow dr, BOMS.Model.S_Warning model)
            {
                if (dr["ID"].ToString() != "")
                {
                    model.ID = int.Parse(dr["ID"].ToString());
                }
                model.EquipID = dr["EquipID"].ToString();
                model.BatteryGroup = dr["BatteryGroup"].ToString();
                if (dr["BatteryID"].ToString() != "")
                {
                    model.BatteryID = int.Parse(dr["BatteryID"].ToString());
                }
                if (dr["WarningTime"].ToString() != "")
                {
                    if (DbHelper.DBType == ConstSet.DBTYPE_MYSQL)
                    {
                        model.WarningTime = ((MySql.Data.Types.MySqlDateTime)dr["WarningTime"]).GetDateTime();
                    }
                    else
                    {
                        model.WarningTime = (DateTime)dr["WarningTime"];
                    }
                }
                if (dr.Table.Columns.Contains("HostName") && dr["HostName"].ToString() != "")
                {
                    model.HostName = dr["HostName"].ToString();
                }
                if (dr.Table.Columns.Contains("IP") && dr["IP"].ToString() != "")
                {
                    model.IP = dr["IP"].ToString();
                }
                if (dr.Table.Columns.Contains("WarningNO") && dr["WarningNO"].ToString() != "")
                {
                    model.WarningNO = (int)dr["WarningNO"];
                }
                //model.WarningNO= (int)dr["WarningNO"];
                model.WarningStatus = dr["WarningStatus"].ToString();
                model.Note = dr["Note"].ToString();
                if (dr["IsActive"].ToString() != "")
                {
                    if ((dr["IsActive"].ToString() == "1") || (dr["IsActive"].ToString().ToLower() == "true"))
                    {
                        model.IsActive = true;
                    }
                    else
                    {
                        model.IsActive = false;
                    }
                }
            }
            /// <summary>
            /// 获得数据列表
            /// </summary>
            public DataSet GetList(string strWhere)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select w.ID,s.HostName as HostName,s.IP,w.EquipID,w.BatteryGroup,w.BatteryID,w.WarningTime,w.WarningNO,w.WarningStatus,w.Note,w.IsActive ");
                strSql.Append(" from S_Warning w left outer join M_SubLans s on w.EquipID=s.Mac ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                strSql.Append(" order by w.WarningTime desc");
                return DbHelper.Query(strSql.ToString());
            }
         
           
            /*
            /// <summary>
            /// 分页获取数据列表
            /// </summary>
            public DataSet GetList(int PageSize,int PageIndex,string strWhere)
            {
                SqlParameter[] parameters = {
                        new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                        new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                        new SqlParameter("@PageSize", SqlDbType.Int),
                        new SqlParameter("@PageIndex", SqlDbType.Int),
                        new SqlParameter("@IsReCount", SqlDbType.Bit),
                        new SqlParameter("@OrderType", SqlDbType.Bit),
                        new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                        };
                parameters[0].Value = "S_Warning";
                parameters[1].Value = "ID";
                parameters[2].Value = PageSize;
                parameters[3].Value = PageIndex;
                parameters[4].Value = 0;
                parameters[5].Value = 0;
                parameters[6].Value = strWhere;	
                return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
            }*/

            #endregion  成员方法
        }
    }